import numpy as np
import pandas as pd

# df = pd.DataFrame(pd.read_excel('name.xlsx'))

df = pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006], 
 "date":pd.date_range('20130102', periods=6),
  "city":['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
 "age":[23,44,54,32,34,32],
 "category":['100-A','100-B','110-A','110-C','210-A','130-F'],
  "price":[1200,np.nan,2133,5433,np.nan,4432]},
  columns =['id','date','city','category','age','price'])


df1=pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006,1007,1008], 
"gender":['male','female','male','female','male','female','male','female'],
"pay":['Y','N','Y','Y','N','Y','N','Y',],
"m-point":[10,12,20,40,40,40,30,20]},
                 columns=['id','gender','pay','m-point'])


# print(df.shape)
# print(df.info())
# print(df.dtypes)
# print(df['id'].dtype)
# print(df.columns)
# print(df.isnull)
# print(df.values)
# print(df.head(6))

# df.fillna(value=0)

print(df.head(10))
# df.fillna(value=0)
# print(df['price'].mean())
# df['price'].fillna(df['price'].mean)

print(df['city'].map(str.strip))
df['city']=df['city'].str.lower()
# df['price'].astype('int')       
df.rename(columns={'category': 'category-size'}) 
df['city']=df['city'].replace('sh', 'shanghai')

print(df.head(10))
print(df1.head(10))
df1.set_index('id', inplace=True)
df.set_index('id', inplace=True)

df2 = df.join(df1,lsuffix="_left", rsuffix="_right",on='id')
df3 = df1.join(df, lsuffix="_left", rsuffix="_right", on='id')
# print(df3.head(10))


df2=df2.sort_values(['price'])
df2 = df2.sort_index()
df2['remark'] = np.where(df2['price'] > 3000 , 'high', 'low')
df2.loc[(df2['city'] == 'beijing') & (df2['age'] >= 10), 'sign'] = 1


print(df2.head(10))

print(df2.loc[1001])
print(df2.iloc[0])
print(df2.loc[:,['date']])
print(df2.iloc[:, [0]])

print( df2.loc[[1001, 1002], ['date', 'city']])
print(df2.iloc[[0, 1], [0, 1]])

print(df2.loc[:,:])
print(df2.iloc[:,:])


print(df2.loc[df2['age']==32])

df4=df2.loc[df2['age']==32]
print(df4)

print(df2.loc[(df2['age'] > 25) | (df2['city'] == 'beijing'), [ 'city','gender']])

print(df2.groupby('city').count())
